﻿#if UNITY_EDITOR
using System;
using UnityEngine;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;


/// 实例
//
// Excel.TraversalCell(filePath, (table, cell) =>
// {
//     Debug.Log(table.name);
//     Debug.Log(cell.Value);
//     return true;
// });
//
//
//
//
// Excel wb = Excel.LoadExcel(f.FullName);
// foreach (var table in wb.sheets)
// {
//     for (int r = table.BeginRow; r <= table.EndRow; r++)
//     {
//         for (int c = table.BeginColumn; c <= table.EndColumn; c++)
//         {
//             var value = table.GetValue(r, c).ToString();
//             Debug.Log(value);
//         }
//     }
// }




/// <summary>
/// 单元格回调
/// </summary>
public delegate bool ExcelCellInvoke(ISheet sheet, ICell cell);

public class Excel
{
    public IWorkbook workbook;
    private FileStream fs;

    /// <summary>
    /// 遍历Excel的所有单元格
    /// </summary>
    /// <param name="path"></param>
    /// <param name="cellCall">return: true 继续遍历 ｜ false 终止遍历</param>
    public static void TraversalCell(string path, ExcelCellInvoke cellCall)
    {
        var excel = LoadExcel(path);
        if (excel == null) return;
        
        for(int s=0; s<excel.workbook.NumberOfSheets; s++)
        {
            var sheet = excel.workbook.GetSheetAt(s);
            for (int r = sheet.FirstRowNum; r <= sheet.LastRowNum; r++)
            {
                var row = sheet.GetRow(r);
                if(row == null) continue;
                
                for (int c = row.FirstCellNum; c <= row.LastCellNum; c++)
                {
                    var cell = row.GetCell(c);
                    if (cell != null)
                    {
                        if (!cellCall(sheet, cell)) return;
                    }
                }
            }
        }
    }
    
    /// <summary>
    /// 加载Excel
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    public static Excel LoadExcel(string path)
    {
        FileStream fs = null;
        try
        {
            FileInfo file = new FileInfo(path);
            bool exist = file.Exists;
            IWorkbook wb;
             fs = File.OpenRead(path);
        
            if (file.Extension == ".xls")
            {
                wb = new HSSFWorkbook(fs);
            }
            else if (file.Extension == ".xlsx")
            {
                wb = new XSSFWorkbook(fs);
            }
            else
            {
                fs.Close();
                return null;
            }
            
            var xls = new Excel(wb);
            fs.Close();
            return xls;
        }
        catch (Exception e)
        {
            if(fs != null)
                fs.Close();
            Debug.LogError("打开excel出错" + path + ":  " + e);
            return null;
        }
        
    }

    /// <summary>
    /// 创建Excel
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    public static Excel CreateExcel(string path)
    {
        try
        {
            FileInfo file = new FileInfo(path);

            IWorkbook wb;
            if (file.Extension == ".xls")
            {
                wb = new HSSFWorkbook();
            }
            else if (file.Extension == ".xlsx")
            {
                wb = new XSSFWorkbook();
            }
            else
            {
                return null;
            }
        
            Excel xls = new Excel(wb);
            return xls;
        }
        catch (Exception e)
        {
            Debug.LogError("创建excel出错" + path + ":  " + e);
            return null;
        }
        
        
    }

    
    /// <summary>
    /// 另存为
    /// </summary>
    /// <param name="path"></param>
    public void SaveAs(string path) 
    {
        try
        {
            using (FileStream fsW = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fsW);
                fsW.Flush();
                fsW.Close();
            }
        }
        catch (Exception e)
        {
            Debug.LogError("创建excel出错" + path + ":  " + e);
        }
    }

    public Excel(IWorkbook wb)
    {
        workbook = wb;
    }

    public void AddSheet(string name)
    {
        workbook.CreateSheet(name);
    }

    public void SetCell(ISheet sheet, int rowIdx, int colIdx, string val)
    {
        var row = sheet.GetRow(rowIdx);
        if (row == null)
        {
            row = sheet.CreateRow(rowIdx);
        }
                        
        var cell = row.GetCell(colIdx);
        if (cell == null)
        {
            cell = row.CreateCell(colIdx);
        }
        
        cell.SetCellValue(val);
    }
    
    public string GetCell(ISheet sheet, int rowIdx, int colIdx)
    {
        var row = sheet.GetRow(rowIdx);
        if (row == null)
        {
            return null;
        }
                        
        var cell = row.GetCell(colIdx);
        if (cell == null)
        {
            return null;
        }
        
        return cell.ToString();
    }
    
}

#endif